CREATE TABLE [dbo].[PromotionCost](
	[PromotionCostId] [int] IDENTITY(1,1) NOT NULL,
	[PromotionId] [int] NOT NULL,
	[DateSubmit] [datetime] NOT NULL,
	[FixedCost] [money] NOT NULL,
 CONSTRAINT [PK_PromotionCost] PRIMARY KEY CLUSTERED 
(
	[PromotionCostId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[PromotionCost]  WITH CHECK ADD  CONSTRAINT [FK_PromotionCost_Promotion] FOREIGN KEY([PromotionId])
REFERENCES [dbo].[Promotion] ([PromotionId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[PromotionCost] CHECK CONSTRAINT [FK_PromotionCost_Promotion]
GO
ALTER PROCEDURE [dbo].[spAPI_Promotion_Submit]
(
	@Token VARCHAR(255),
	@PromotionId INT
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	
	DECLARE @CampaignBudget MONEY, @FixedCost MONEY
	DECLARE @CreditCardNo VARCHAR(20)	
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
	IF @IdResult = 1
		IF EXISTS(SELECT 1 FROM Promotion (NOLOCK) WHERE BusinessId = @BusinessId AND PromotionId = @PromotionId)
		BEGIN
			UPDATE Promotion
			SET
				Ready = 1,
				PromotionNumber = (select isnull(max(PromotionNumber) + 1, 1) from Promotion (nolock) WHERE BusinessId = @BusinessId),
				CheckinCode = CONVERT (VARCHAR(4), (1000+ ABS(CHECKSUM(NewId())) % 8999)) 
			WHERE (PromotionId = @PromotionId)
			
			EXEC spPromotion_StartStop @PromotionId = @PromotionId
			
			SELECT @CampaignBudget = ISNULL(CampaignBudget, 0), @FixedCost = ISNULL(FixedCost, 0)
			FROM Promotion (NOLOCK)
			WHERE (PromotionId = @PromotionId)
			
			INSERT INTO PromotionCost
			(PromotionId, DateSubmit, FixedCost)
			VALUES (@PromotionId, GETUTCDATE(), @FixedCost)

			-- Temporary write to pay
			SELECT @CreditCardNo = CreditCardNo
			FROM Business (NOLOCK)
			WHERE (BusinessId = @BusinessId)
						
			IF @CampaignBudget > 0
				INSERT INTO PromotionPay
				(PromotionId, DatePay, Amount, CreditCardNo)
				VALUES (@PromotionId, GETUTCDATE(), @CampaignBudget, @CreditCardNo)
			----
		END	
		ELSE
			SET @IdResult = -1

	SELECT @IdResult AS IdResult
END
GO
ALTER FUNCTION [dbo].[fnTransaction_List]
(
  @BusinessId INT,
  @PromotionId INT
)
RETURNS @retList TABLE
(
	RowId INT IDENTITY(1, 1),
	PromotionId INT,
	DateOperation DATETIME,
	TypeOperation VARCHAR(50),
	Amount MONEY,
	TransactionId INT,
	SortFlag INT,
	Balance MONEY
)	
AS  
BEGIN
	INSERT INTO @retList
	(PromotionId, DateOperation, TypeOperation, Amount, TransactionId, SortFlag)
		
	SELECT PromotionPush.PromotionId, 
		DATEADD(DD, (DATEDIFF(DD, 0, PromotionPush.DatePush)), 0) AS DateOperation, 
		'Charge', -SUM(PromotionPush.RatePerPush), -1 AS TransactionId, 2 AS SortFlag
	FROM Promotion (NOLOCK) INNER JOIN PromotionPush (NOLOCK) ON Promotion.PromotionId = PromotionPush.PromotionId
	WHERE (Promotion.BusinessId = @BusinessId)
		AND (Promotion.PromotionId = @PromotionId OR @PromotionId IS NULL)
	GROUP BY PromotionPush.PromotionId, DATEADD(DD, (DATEDIFF(DD, 0, PromotionPush.DatePush)), 0)
		
	UNION ALL
	
	SELECT PromotionCost.PromotionId, 
		DATEADD(DD, (DATEDIFF(DD, 0, PromotionCost.DateSubmit)), 0) AS DateOperation, 
		'FlatFee', -PromotionCost.FixedCost, PromotionCost.PromotionCostId AS TransactionId, 1 AS SortFlag
	FROM Promotion (NOLOCK) INNER JOIN PromotionCost (NOLOCK) ON Promotion.PromotionId = PromotionCost.PromotionId
	WHERE (Promotion.BusinessId = @BusinessId)
		AND (Promotion.PromotionId = @PromotionId OR @PromotionId IS NULL)
				
	UNION ALL
	
	SELECT PromotionPay.PromotionId, 
		DATEADD(DD, (DATEDIFF(DD, 0, PromotionPay.DatePay)), 0) AS DateOperation, 
		'CCard', PromotionPay.Amount, PromotionPay.PromotionPayId AS TransactionId, 0 AS SortFlag
	FROM Promotion (NOLOCK) INNER JOIN PromotionPay (NOLOCK) ON Promotion.PromotionId = PromotionPay.PromotionId
	WHERE (Promotion.BusinessId = @BusinessId)
		AND (Promotion.PromotionId = @PromotionId OR @PromotionId IS NULL)
		
	ORDER BY PromotionId, DateOperation, SortFlag, TransactionId
		
	DECLARE @RowId INT, @PromotionIdLocal INT, @PromotionIdPrev INT, @Amount MONEY, @RunningTotal MONEY
	SET @PromotionIdPrev = 0
		
	DECLARE cursorOperations 
	CURSOR FOR 
	SELECT RowId, Amount, PromotionId
	FROM @retList
	ORDER BY RowId 
		
	OPEN cursorOperations
	FETCH FROM cursorOperations INTO @RowId, @Amount, @PromotionIdLocal
		
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @PromotionIdLocal <> @PromotionIdPrev
			SET @RunningTotal = 0
				
		SET @RunningTotal = @RunningTotal + @Amount
		SET @PromotionIdPrev = @PromotionIdLocal
			
		UPDATE @retList 
		SET Balance = @RunningTotal 
		WHERE RowId = @RowId
			
		FETCH FROM cursorOperations INTO @RowId, @Amount, @PromotionIdLocal
	END
		
	CLOSE cursorOperations
	DEALLOCATE cursorOperations
	
	RETURN
END
GO
ALTER PROCEDURE [dbo].[spAPI_Billing_Transaction_History]
(
	@Token VARCHAR(255),
	@PromotionId INT = NULL
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT
	DECLARE @Result XML
	
	DECLARE @BusinessId INT
	DECLARE @Balance MONEY
		
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
	IF @IdResult = 1
	BEGIN
		CREATE TABLE #tmpOperations
		(
			RowId INT,
			PromotionId INT,
			DateOperation DATETIME,
			TypeOperation VARCHAR(50),
			Amount MONEY,
			TransactionId INT,
			SortFlag INT,
			Balance MONEY
		)
		
		INSERT INTO #tmpOperations
		(RowId, PromotionId, DateOperation, TypeOperation, Amount, TransactionId, SortFlag, Balance)
		SELECT RowId, PromotionId, DateOperation, TypeOperation, Amount, TransactionId, SortFlag, Balance
		FROM dbo.fnTransaction_List(@BusinessId, @PromotionId)
						
		SELECT @Balance = SUM(Amount)
		FROM #tmpOperations
		
		SET @Balance = ISNULL(@Balance, 0)
		
		SET @Result = (SELECT @Balance AS CurrentAccoutnBalance,
							(SELECT
								#tmpOperations.TransactionId
								, #tmpOperations.DateOperation AS [Date]
								, #tmpOperations.TypeOperation AS [Type]
								, case when Promotion.PromotionNumber < 10 then '0' else '' end
									+ cast(Promotion.PromotionNumber as varchar) AS [Description]
								, Promotion.Name as PromotionName
								, CASE WHEN #tmpOperations.TypeOperation IN ('Charge', 'FlatFee') THEN -#tmpOperations.Amount ELSE 0 END AS Debit
								, CASE WHEN #tmpOperations.TypeOperation = 'CCard' THEN #tmpOperations.Amount ELSE 0 END AS Credit
								, #tmpOperations.Balance
								, #tmpOperations.PromotionId
							FROM #tmpOperations LEFT JOIN Promotion ON #tmpOperations.PromotionId = Promotion.PromotionId
							ORDER BY #tmpOperations.DateOperation DESC, #tmpOperations.SortFlag DESC
							FOR XML PATH ('Transaction'), ROOT('Transactions'), TYPE)
						FOR XML PATH ('TransactionHistory'))
	END
		
	SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result 
END
GO
ALTER PROCEDURE [dbo].[spAPI_Business_GetPromotionDetails]
(
	@Token VARCHAR(255),
	@PromotionId INT
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
	IF @IdResult = 1
		IF EXISTS(SELECT 1 FROM Promotion WHERE BusinessId = @BusinessId AND PromotionId = @PromotionId)
		BEGIN
			SELECT @Result = (SELECT 
									Promotion.[PromotionId],
									Promotion.[BusinessId],
									Promotion.[ProximityRadiusId],
									pr.FromRadius as ProximityFromRadius,
									pr.ToRadius as ProximityToRadius,
									Promotion.[CustomRange],
									Promotion.[Over200MilesFlag],
									Promotion.[RegisteredOutsideOfZip],
									Promotion.[RegisteredOutsideOfState],
									Promotion.[RegisteredOutsideOfCity],
									Promotion.[BaseCostTimingId],
									bct.Timing as BaseCostTimingName,
									Promotion.[BaseCostTimingScheduledAt],
									Promotion.[BaseCostTimingStarts],
									Promotion.[BaseCostTimingEnds],
									Promotion.[CampaignBudget],
									Promotion.[Name],
									Promotion.[PromotionText],
									Promotion.[PromixityRadiusFixedCost],
									Promotion.[PromixityRadiusRatePerPush],									
									Promotion.[CostTimingFixedCost],
									Promotion.[CostTimingRatePerPush],
									Promotion.BaseCostTimingRatePerPush,
									Promotion.BaseCostTimingFixedCost,
									Promotion.[StatusCode],
									Promotion.[FixedCost],
									Promotion.[RatePerPush],
									Promotion.[CheckinCode],
									Promotion.[Deleted],
									Promotion.[Ready],									
									ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
									CONVERT(VARCHAR(20), ISNULL(vwReview.AvgRating, 0)) + ',' + 
									CASE WHEN ISNULL(vwReview.AvgRating, 0) BETWEEN 0 AND 2 THEN 'Y' 
										WHEN ISNULL(vwReview.AvgRating, 0) BETWEEN 3 AND 4 THEN 'G' 
									ELSE 'R' END AS Rating,
									ISNULL(vwReview.CountReviews, 0) AS Reviews,
																		
									CASE WHEN (Promotion.RatePerPush = 0) THEN 0
									ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - vwPromotionCost.Amount) / Promotion.RatePerPush)) 
									END AS MaxPushes,
									
									ISNULL(vwPushes.Amount, 0) + ISNULL(vwPromotionCost.Amount, 0) AS SpentMoney, 
									ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
									ISNULL(vwPay.Amount, 0) - ISNULL(vwPushes.Amount, 0) - ISNULL(vwPromotionCost.Amount, 0) AS UnusedMoney,
									
									CASE WHEN (Promotion.RatePerPush = 0) THEN 0
									ELSE CONVERT(INT, FLOOR((ISNULL(vwPay.Amount, 0) - ISNULL(vwPushes.Amount, 0) - ISNULL(vwPromotionCost.Amount, 0)) / Promotion.RatePerPush)) 
									END AS UnusedPushes,
									
									case
										when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getutcdate(), Promotion.[BaseCostTimingEnds]))
										else -1 end as MinutesLeft,
										
									(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name,
											dbo.fnAPI_Category_Path(Category.CategoryId) AS FullPath
										FROM PromotionCategory LEFT JOIN Category ON PromotionCategory.CategoryId = Category.CategoryId
										WHERE PromotionCategory.PromotionId = Promotion.PromotionId
										FOR XML AUTO, TYPE) AS Categories
								FROM Promotion LEFT JOIN BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
											LEFT JOIN ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId
											LEFT JOIN (SELECT BusinessId, 
															COUNT(*) AS CountReviews, ROUND(AVG(CONVERT(FLOAT, Rating)), 0) AS AvgRating 
														FROM BusinessReview
														GROUP BY BusinessId) AS vwReview ON Promotion.BusinessId = vwReview.BusinessId,
											
									(SELECT COUNT(*) AS CountPushes, SUM(RatePerPush) AS Amount
									FROM PromotionPush
									WHERE PromotionId = @PromotionId) AS vwPushes,
									
									(SELECT SUM(Amount) AS Amount
									FROM PromotionPay
									WHERE PromotionId = @PromotionId) AS vwPay,
									
									(SELECT SUM(FixedCost) AS Amount
									FROM PromotionCost
									WHERE PromotionId = @PromotionId) AS vwPromotionCost,
									
									(SELECT COUNT(*) AS CheckIns 
									FROM PromotionCheckin 
									WHERE PromotionId = @PromotionId) AS vwCheckin
												
								WHERE (Promotion.PromotionId = @PromotionId)
								FOR XML PATH ('Promotion'))
		END								
		ELSE
			SET @IdResult = -1
	
	SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
GO
ALTER PROCEDURE [dbo].[spAPI_Promotion_Push]
-- Use in other SP!!!
-- Not for call directly
(
	@UserId INT,
	@Latitude FLOAT,
	@Longitude FLOAT,
	
	@Result XML OUT
)
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	DECLARE @RegState VARCHAR(5), @ZipCode VARCHAR(5), @RegCity VARCHAR(50)
	
	SELECT @RegState = RegState, @ZipCode = ZipCode, @RegCity = RegCity
	FROM [User] (NOLOCK)
	WHERE UserId = @UserId
	
	IF @RegState = ''
		SET @RegState = NULL
	IF @ZipCode = ''
		SET @ZipCode = NULL
	IF @RegCity = ''
		SET @RegCity = NULL
	
	CREATE TABLE #tmpPromotions
	(
		PromotionId INT,
		RatePerPush MONEY
	)
		
	BEGIN TRANSACTION
			INSERT INTO #tmpPromotions
			(PromotionId, RatePerPush)
			SELECT Promotion.PromotionId, Promotion.RatePerPush
			FROM Promotion (NOLOCK) LEFT JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
						LEFT JOIN ProximityRadius (NOLOCK) ON Promotion.ProximityRadiusId = ProximityRadius.ProximityRadiusId
						LEFT JOIN BlockBusiness (NOLOCK) AS bb ON bb.BusinessId = Business.BusinessId AND bb.UserId = @UserId
						LEFT JOIN BlockPromotion (NOLOCK) AS bp ON bp.PromotionId = Promotion.PromotionId AND bp.UserId = @UserId
			WHERE (NOT EXISTS(SELECT 1 FROM PromotionPush (NOLOCK) WHERE (PromotionPush.UserId = @UserId) AND (PromotionPush.PromotionId = Promotion.PromotionId)))
				AND (dbo.fnCalculateDistance(@Latitude, @Longitude, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, ProximityRadius.ToRadiusFeet / 5280))
				--AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) >= CONVERT(FLOAT, ProximityRadius.FromRadiusFeet / 5280))		
				AND (Promotion.Deleted = 0)
				AND (Promotion.Ready = 1)
				AND (Promotion.StatusCode = 'ACT')
				AND (bb.UserId IS NULL)
				AND (bp.UserId IS NULL)
				AND (Business.IsActive = 1)
				AND (NOT EXISTS(SELECT 1 
								FROM PromotionCategory (NOLOCK) AS pc INNER JOIN fnAPI_BlockCategory_All(@UserId) AS bc ON pc.CategoryId = bc.CategoryId
								WHERE pc.PromotionId = Promotion.PromotionId))
				AND (EXISTS(SELECT 1 
							FROM PromotionCategory AS pc INNER JOIN fnAPI_UserCategory_All(@UserId) AS uc ON pc.CategoryId = uc.CategoryId
							WHERE pc.PromotionId = Promotion.PromotionId))
				AND ((Promotion.RegisteredOutsideOfState = 0) OR ((Promotion.RegisteredOutsideOfState = 1) AND (ISNULL(@RegState, Business.StateCode) <> Business.StateCode)))
				AND ((Promotion.RegisteredOutsideOfZip = 0) OR ((Promotion.RegisteredOutsideOfZip = 1) AND (ISNULL(@ZipCode, Business.ZipCode) <> Business.ZipCode)))
				AND ((Promotion.RegisteredOutsideOfCity = 0) OR ((Promotion.RegisteredOutsideOfCity = 1) AND (ISNULL(@RegCity, Business.City) <> Business.City)))
				
			SELECT @Result = (SELECT Promotion.PromotionId, Promotion.PromotionText, Business.BusinessId, Business.BusinessName,
								STR(dbo.fnCalculateDistance(@Latitude, @Longitude, Business.Latitude, Business.Longitude), 38, 2) AS Distance,
								CONVERT(VARCHAR(20), ISNULL(vwReview.AvgRating, 0)) + ',' + 
									CASE WHEN ISNULL(vwReview.AvgRating, 0) BETWEEN 0 AND 2 THEN 'Y' 
										WHEN ISNULL(vwReview.AvgRating, 0) BETWEEN 3 AND 4 THEN 'G' 
									ELSE 'R' END AS Rating,
								ISNULL(vwReview.CountReviews, 0) AS Reviews, 15 AS PriceRange,
								Business.StreetAddress1 + ' ' + Business.StreetAddress2 AS [Address],
								Business.Latitude, Business.Longitude,
								Promotion.PromotionText, Business.[Description], 
								
								Business.BusinessPhone, Business.CellPhone, 
								Business.TollFreePhone, Business.Web,
								
								(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name
									FROM BusinessCategory (NOLOCK) LEFT JOIN Category (NOLOCK) ON BusinessCategory.CategoryId = Category.CategoryId
									WHERE BusinessCategory.BusinessId = Business.BusinessId
									FOR XML AUTO, TYPE) AS Categories,
								(SELECT [WeekDay] AS Name, 
									dbo.fnAPI_Format_Time(FromTime) AS Start, 
									dbo.fnAPI_Format_Time(ToTime) AS [End], 
									Closed
								FROM BusinessHours AS [Day] (NOLOCK)
								WHERE [Day].BusinessId = Business.BusinessId
								FOR XML AUTO, TYPE) AS BusinessHours
							FROM #tmpPromotions INNER JOIN Promotion (NOLOCK) ON #tmpPromotions.PromotionId = Promotion.PromotionId
											LEFT JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
											LEFT JOIN (SELECT BusinessId, 
													COUNT(*) AS CountReviews, ROUND(AVG(CONVERT(FLOAT, Rating)), 0) AS AvgRating 
													FROM BusinessReview (NOLOCK)
													GROUP BY BusinessId) AS vwReview ON Business.BusinessId = vwReview.BusinessId
							FOR XML PATH ('Promotion'))
							
			INSERT INTO PromotionPush
			(PromotionId, UserId, DatePush, Latitude, Longitude, RatePerPush)
			SELECT PromotionId, @UserId, GETUTCDATE(), @Latitude, @Longitude, RatePerPush
			FROM #tmpPromotions
			--WHERE (NOT EXISTS(SELECT 1 FROM PromotionPush WHERE (PromotionPush.UserId = @UserId) AND (PromotionPush.PromotionId = #tmpPromotions.PromotionId)))
			
			UPDATE Promotion
			SET StatusCode = 'COM'
			FROM #tmpPromotions LEFT JOIN (SELECT PromotionId, SUM(RatePerPush) AS UsedBudget
											FROM PromotionPush
											GROUP BY PromotionId) AS vwPush ON #tmpPromotions.PromotionId = vwPush.PromotionId
								LEFT JOIN (SELECT PromotionId, SUM(FixedCost) AS Amount
											FROM PromotionCost
											GROUP BY PromotionId) AS vwPromotionCost ON #tmpPromotions.PromotionId = vwPromotionCost.PromotionId
			WHERE (Promotion.PromotionId = #tmpPromotions.PromotionId)
				AND (Promotion.CampaignBudget - ISNULL(vwPush.UsedBudget, 0) - ISNULL(vwPromotionCost.Amount, 0) < Promotion.RatePerPush)
	COMMIT TRANSACTION
		
	DROP TABLE #tmpPromotions
END
GO
ALTER PROCEDURE [dbo].[spAPI_User_GetPromotionDetails]
(
	@Token VARCHAR(255),
	@PromotionId INT
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT
	DECLARE @UserId INT
	
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult = 1
		IF EXISTS(SELECT 1 FROM PromotionPush WHERE UserId = @UserId AND PromotionId = @PromotionId)
		BEGIN
			SELECT @Result = (SELECT 
									Promotion.[PromotionId],
									Promotion.[BusinessId],
									Promotion.[ProximityRadiusId],
									pr.FromRadius as ProximityFromRadius,
									pr.ToRadius as ProximityToRadius,
									Promotion.[CustomRange],
									Promotion.[Over200MilesFlag],
									Promotion.[RegisteredOutsideOfZip],
									Promotion.[RegisteredOutsideOfState],
									Promotion.[RegisteredOutsideOfCity],
									Promotion.[BaseCostTimingId],
									bct.Timing as BaseCostTimingName,
									Promotion.[BaseCostTimingScheduledAt],
									Promotion.[BaseCostTimingStarts],
									Promotion.[BaseCostTimingEnds],
									Promotion.[CampaignBudget],
									Promotion.[Name],
									Promotion.[PromotionText],
									Promotion.[PromixityRadiusFixedCost],
									Promotion.[PromixityRadiusRatePerPush],									
									Promotion.[CostTimingFixedCost],
									Promotion.[CostTimingRatePerPush],
									Promotion.BaseCostTimingRatePerPush,
									Promotion.BaseCostTimingFixedCost,
									Promotion.[StatusCode],
									Promotion.[FixedCost],
									Promotion.[RatePerPush],
									Promotion.[CheckinCode],
									Promotion.[Deleted],
									Promotion.[Ready],									
									ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
									
									CASE WHEN (Promotion.RatePerPush = 0) THEN 0
									ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - vwPromotionCost.Amount) / Promotion.RatePerPush)) 
									END AS MaxPushes,
									
									ISNULL(vwPushes.Amount, 0) + ISNULL(vwPromotionCost.Amount, 0) AS SpentMoney, 
									ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
									ISNULL(vwPay.Amount, 0) - ISNULL(vwPushes.Amount, 0) - ISNULL(vwPromotionCost.Amount, 0) AS UnusedMoney,
									
									CASE WHEN (Promotion.RatePerPush = 0) THEN 0
									ELSE CONVERT(INT, FLOOR((ISNULL(vwPay.Amount, 0) - ISNULL(vwPushes.Amount, 0) - ISNULL(vwPromotionCost.Amount, 0)) / Promotion.RatePerPush)) 
									END AS UnusedPushes,
									
									case
										when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getdate(), Promotion.[BaseCostTimingEnds]))
										else -1 end as MinutesLeft,
										
									(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name,
											dbo.fnAPI_Category_Path(Category.CategoryId) AS FullPath
										FROM PromotionCategory LEFT JOIN Category ON PromotionCategory.CategoryId = Category.CategoryId
										WHERE PromotionCategory.PromotionId = Promotion.PromotionId
										FOR XML AUTO, TYPE) AS Categories
								FROM Promotion left join BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
												left join ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId,
												
									(SELECT COUNT(*) AS CountPushes, SUM(RatePerPush) AS Amount
									FROM PromotionPush
									WHERE PromotionId = @PromotionId) AS vwPushes,
									
									(SELECT SUM(Amount) AS Amount
									FROM PromotionPay
									WHERE PromotionId = @PromotionId) AS vwPay,
									
									(SELECT SUM(FixedCost) AS Amount
									FROM PromotionCost
									WHERE PromotionId = @PromotionId) AS vwPromotionCost,
									
									(SELECT COUNT(*) AS CheckIns 
									FROM PromotionCheckin 
									WHERE PromotionId = @PromotionId) AS vwCheckin
												
								WHERE (Promotion.PromotionId = @PromotionId)
								FOR XML PATH ('Promotion'))
		END								
		ELSE
			SET @IdResult = -1
	
	SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
GO
ALTER PROCEDURE [dbo].[spBusiness_GetBilling]
	@BusinessId INT
AS
BEGIN
	SET NOCOUNT ON

	CREATE TABLE #tmpOperations
	(
		RowId INT,
		PromotionId INT,
		DateOperation DATETIME,
		TypeOperation VARCHAR(50),
		Amount MONEY,
		TransactionId INT,
		SortFlag INT,
		Balance MONEY
	)
		
	INSERT INTO #tmpOperations
	(RowId, PromotionId, DateOperation, TypeOperation, Amount, TransactionId, SortFlag, Balance)
	SELECT RowId, PromotionId, DateOperation, TypeOperation, Amount, TransactionId, SortFlag, Balance
	FROM dbo.fnTransaction_List(@BusinessId, NULL)
	
	SELECT #tmpOperations.DateOperation AS [Date],
		#tmpOperations.TypeOperation AS [Type],
		'Promo ' + CASE WHEN Promotion.PromotionNumber < 10 THEN '0' ELSE '' END + 
		CAST(Promotion.PromotionNumber AS VARCHAR) AS [Description],
		CASE WHEN #tmpOperations.TypeOperation IN ('Charge', 'FlatFee') THEN -#tmpOperations.Amount ELSE 0 END AS Debit,
		CASE WHEN #tmpOperations.TypeOperation = 'CCard' THEN #tmpOperations.Amount ELSE 0 END AS Credit,
		#tmpOperations.Balance, '' AS Comments
	FROM #tmpOperations LEFT JOIN Promotion ON #tmpOperations.PromotionId = Promotion.PromotionId
	ORDER BY #tmpOperations.DateOperation DESC, #tmpOperations.SortFlag DESC
	
	DROP TABLE #tmpOperations
END
GO
ALTER PROCEDURE [dbo].[spPromotion_GetById]
(
	@PromotionId INT
)
AS
BEGIN
	SET NOCOUNT ON


	SELECT Promotion.*,
		ISNULL(vwPushes.UsedBudget, 0) AS UsedBudget, 
		Promotion.CampaignBudget - ISNULL(vwPushes.UsedBudget, 0) - ISNULL(vwPromotionCost.Amount, 0) AS RemainingBudget,
		ISNULL(vwPushes.Pushes, 0) AS Pushes, ISNULL(vwCheckin.CheckIns, 0) AS CheckIns
		
	FROM Promotion (NOLOCK) LEFT JOIN BaseCostTiming (NOLOCK) ON Promotion.BaseCostTimingId = BaseCostTiming.BaseCostTimingId
							LEFT JOIN (SELECT PromotionId, COUNT(*) AS Pushes, SUM(RatePerPush) AS UsedBudget
										FROM PromotionPush (NOLOCK)
										GROUP BY PromotionId) AS vwPushes ON Promotion.PromotionId = vwPushes.PromotionId
							LEFT JOIN (SELECT PromotionId, COUNT(*) AS CheckIns 
										FROM PromotionCheckin (NOLOCK)
										GROUP BY PromotionId) AS vwCheckin ON Promotion.PromotionId = vwCheckin.PromotionId
							LEFT JOIN (SELECT PromotionId, SUM(FixedCost) AS Amount
										FROM PromotionCost (NOLOCK)
										GROUP BY PromotionId) AS vwPromotionCost ON Promotion.PromotionId = vwPromotionCost.PromotionId
	WHERE Promotion.PromotionId = @PromotionId
END
GO
ALTER PROCEDURE [dbo].[spPromotion_GetList]
(
	@BusinessId INT
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT Promotion.*,
		ISNULL(vwPushes.UsedBudget, 0) AS UsedBudget, 
		Promotion.CampaignBudget - ISNULL(vwPushes.UsedBudget, 0) - ISNULL(vwPromotionCost.Amount, 0) AS RemainingBudget,
		ISNULL(vwPushes.Pushes, 0) AS Pushes, ISNULL(vwCheckin.CheckIns, 0) AS CheckIns
	FROM Promotion (NOLOCK) LEFT JOIN BaseCostTiming (NOLOCK) ON Promotion.BaseCostTimingId = BaseCostTiming.BaseCostTimingId
							LEFT JOIN (SELECT PromotionId, COUNT(*) AS Pushes, SUM(RatePerPush) AS UsedBudget
										FROM PromotionPush (NOLOCK)
										GROUP BY PromotionId) AS vwPushes ON Promotion.PromotionId = vwPushes.PromotionId
							LEFT JOIN (SELECT PromotionId, COUNT(*) AS CheckIns 
										FROM PromotionCheckin (NOLOCK)
										GROUP BY PromotionId) AS vwCheckin ON Promotion.PromotionId = vwCheckin.PromotionId
							LEFT JOIN (SELECT PromotionId, SUM(FixedCost) AS Amount
										FROM PromotionCost (NOLOCK)
										GROUP BY PromotionId) AS vwPromotionCost ON Promotion.PromotionId = vwPromotionCost.PromotionId										
	WHERE Promotion.BusinessId = @BusinessId
END
GO